﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class OrdersBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        private string nice_food_name = "CONCAT( food_name, IF( suffix IS NULL || suffix like '' ,  '', CONCAT(  '[', suffix,  ']' ) ) )";
        private string nice_drfood_name = "CONCAT( dr.food_name, IF( dr.suffix IS NULL || dr.suffix like '' ,  '', CONCAT(  '[', dr.suffix,  ']' ) ) )";
        public OrdersBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public List<Orders> GetOrdersByOrderNum(string order_num)
        {
            List<Orders> list = new List<Orders>();
            string sql = "select * from dc_orders where order_num='" + order_num + "' order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Orders orders = new Orders((int)reader["id"], (string)reader["order_num"], (string)reader["food_name"], (float)reader["price"], (float)reader["number"], (string)reader["taste"], (int)reader["returned"], (int)reader["printed"], (float)reader["final_price"]);
                    list.Add(orders);
                }

                return list;
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetOrdersDataTableByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_food_name + " 品名,number 数量,unit 单位,price 单价（元）,round(price*number,1) as 总额（元）,type 种类 from dc_orders where order_num in(" + w + ") order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetHisOrdersDataTableByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_food_name + " 品名,number 数量,unit 单位,price 单价（元）,round(price*number,1) as 总额（元）,type 种类 from dc_hisorders where order_num in(" + w + ") order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }
        public DataTable GetOrdersDataTableByOrderNum(string order_num)
        {
            string sql = "select " + nice_food_name + " 品名,number 数量,unit 单位,price 单价（元）,round(price*number,1) as 总额（元）,type 种类 from dc_orders where order_num='" + order_num + "' order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetAllReturnedSumByOrderNumAndType(List<string> order_nums, string type)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select sum(round(price*count,1)) from dc_returned where order_num in("+w+") and type='" + type + "'";
             try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetReturnedOrdersSum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select sum(round(count*price,1)) from dc_returned where order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                return float.Parse(cmd.ExecuteScalar() + "");
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetHisReturnedOrdersSum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select sum(round(count*price,1)) from dc_hisreturned where order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                return float.Parse(cmd.ExecuteScalar() + "");
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetReturnedOrdersDataTableByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_drfood_name + " 品名,count 数量,dos.unit 单位,round(count*dos.price,1) 总额,time 退菜时间,reason 原因 from dc_returned dr join dc_orders dos on dr.order_num=dos.order_num and concat_ws('',dr.food_name,dr.suffix)=concat_ws('',dos.food_name,dos.suffix) where dr.order_num in(" + w + ") order by time";
            
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetHisReturnedOrdersDataTableByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_drfood_name + " 品名,count 数量,dos.unit 单位,round(count*dos.price,1) 总额,time 退菜时间,reason 原因 from dc_hisreturned dr join dc_hisorders dos on dr.order_num=dos.order_num and  concat_ws('',dr.food_name,dr.suffix)=concat_ws('',dos.food_name,dos.suffix) where dr.order_num in(" + w + ") order by time";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }
        public DataTable GetReturnedOrdersDataTableByOrderNum(string order_num)
        {
            string sql = "select " + nice_food_name + " 品名,count 数量,time 退菜时间,reason 原因 from dc_returned where order_num='" + order_num + "' order by time";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public List<string> GetTypeByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            List<string> list = new List<string>();
            string sql = "select type from dc_orders where order_num in(" + w + ") group by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add((string)reader["type"]);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }
        }

        public List<string> GetHisTypeByOrderNum(List<string> order_nums)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            List<string> list = new List<string>();
            string sql = "select type from dc_hisorders where order_num in(" + w + ") group by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add((string)reader["type"]);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetOrdersDataTableByOrderNumAndType(List<string> order_nums, string type)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_food_name + " 品名,sum(round(number,1)) 数量,unit 单位,price 单价（元）,sum(round(price*number,1)) as 总额（元）,type 种类 from dc_orders where order_num in(" + w + ") and type='" + type + "' group by concat_ws('',food_name,suffix)";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public DataTable GetHisOrdersDataTableByOrderNumAndType(List<string> order_nums, string type)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select " + nice_food_name + " 品名,sum(round(number,1)) 数量,unit 单位,price 单价（元）,sum(round(price*number,1)) as 总额（元）,type 种类 from dc_hisorders where order_num in(" + w + ") and type='" + type + "' group by  concat_ws('',food_name,suffix)";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetAllPriceByOrderNumOrType(List<string> order_nums, string type)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "";
            if (type != "")
                sql = "select round(sum(price*number),1) from dc_orders where order_num in(" + w + ") and type='" + type + "'";
            else
                sql = "select round(sum(price*number),1) from dc_orders where order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetHisAllPriceByOrderNumOrType(List<string> order_nums, string type)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "";
            if (type != "")
                sql = "select round(sum(price*number),1) from dc_hisorders where order_num in(" + w + ") and type='" + type + "'";
            else
                sql = "select round(sum(price*number),1) from dc_hisorders where order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetAllPriceByOrderNumAndFieldLike(List<string> order_nums, string field, string like)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(price*number),1) from dc_orders where order_num in(" + w + ") and " + field + " like'%" + like + "%'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetAllPriceByOrderNumAndWhereString(List<string> order_nums, string where)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(price*number),1) from dc_orders where order_num in(" + w + ") and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetAllSpecialPriceByOrderNumAndWhereString(List<string> order_nums, int menutype)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(do.price*do.number),1) from dc_orders do join  dc_menu dm on dm.food_name=do.food_name where dm.menutype=" + menutype + " and do.order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetAllSpecialReturnedPriceByOrderNumAndWhereString(List<string> order_nums, int menutype)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(do.price*do.count),1) from dc_returned do join  dc_menu dm on dm.food_name=do.food_name where dm.menutype=" + menutype + " and do.order_num in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetAllReturnedPriceByOrderNumAndWhereString(List<string> order_nums, string where)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(price*count),1) from dc_returned where order_num in(" + w + ") and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetAllReturnedPriceByOrderNumAndFieldLike(List<string> order_nums, string field, string like)
        {
            string w = "";
            for (int i = 0; i < order_nums.Count; i++)
            {
                w += "'" + order_nums[i] + "'";
                if (i != order_nums.Count - 1)
                    w += ",";
            }
            string sql = "select round(sum(price*count),1) from dc_returned where order_num in(" + w + ") and " + field + " like'%" + like + "%'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                Object o = cmd.ExecuteScalar();
                if (o != null)
                {
                    if (o.ToString() != "")
                        return float.Parse(o.ToString());
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
    }
}
